﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using FX.CC.Andon.Entity.BasicDataManage;
using FX.CC.Andon.Utility.Common;
using FX.CC.Andon.Utility.DataBase;

namespace FX.CC.Andon.Service.BasicDataManage
{
    public class LineService : DbHelperBase
    {
        public int GetLineCount(LineQcEntity qcmodel)
        {
            var count = 0;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(*)  AS [count] FROM UT_Line where 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.WSID))
            {
                strSql.Append(" and WSID='" + qcmodel.WSID + "'");
            }

            if (!string.IsNullOrEmpty(qcmodel.LineCode))
            {
                strSql.Append(" and A.LineCode = '" + qcmodel.LineCode + "' ");
            }

            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["count"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public List<LineEntity> GetLineList()
        {
            var list = new List<LineEntity>();
            var strSql = new StringBuilder();
            strSql.Append("SELECT LineID, LineCode, LineName FROM UT_Line order by InputTime, LineCode");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new LineEntity
                            {
                                LineID = ReaderHelper.GetValue<Guid>(reader, "LineID"),
                                LineCode = ReaderHelper.GetValue<string>(reader, "LineCode"),
                                LineName = ReaderHelper.GetValue<string>(reader, "LineName")
                            };
                            list.Add(model);
                        }
                    }

                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public IList<LineEntity> GetLineList(LineQcEntity qcmodel)
        {
            var list = new List<LineEntity>();
            var strSql = new StringBuilder();
            object obj = DBNull.Value;
            int start = qcmodel.PageSize * qcmodel.PageIndex + 1;
            int end = qcmodel.PageSize + start - 1;
            strSql.Append(" SELECT T.* FROM( ");
            strSql.Append(" SELECT row_number() over (order by A.InputTime) AS idx,A.LineID,A.LineCode,A.LineName,A.WSID,B.WSCode,B.WSName FROM UT_Line AS A ");
            strSql.Append(" INNER JOIN UT_WorkShop AS  B ON A.WSID=B.WSID WHERE 1=1 ");
            if (!string.IsNullOrEmpty(qcmodel.WSID))
            {
                strSql.Append(" and A.WSID = '" + qcmodel.WSID + "' ");
            }

            if (!string.IsNullOrEmpty(qcmodel.LineCode))
            {
                strSql.Append(" and A.LineCode = '" + qcmodel.LineCode + "' ");
            }

            strSql.Append(" ) AS T ");
            strSql.Append(" Where T.idx between " + start + " and " + end);
            if (qcmodel.SortField != "" && qcmodel.SortOrder != "")
            {
                strSql.Append(" Order by " + qcmodel.SortField + " " + qcmodel.SortOrder);
            }
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        while (reader.Read())
                        {
                            var model = new LineEntity();
                            model.LineID = ReaderHelper.GetValue<Guid>(reader, "LineID");
                            model.LineCode = ReaderHelper.GetValue<string>(reader, "LineCode");
                            model.LineName = ReaderHelper.GetValue<string>(reader, "LineName");
                            model.WSID = ReaderHelper.GetValue<Guid>(reader, "WSID");
                            model.WSCode = ReaderHelper.GetValue<string>(reader, "WSCode");
                            model.WSName = ReaderHelper.GetValue<string>(reader, "WSName"); 
                            list.Add(model);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return list;
        }

        public bool AddLine(LineEntity model)
        {
            var result = false;
            var strSql = new StringBuilder();
            strSql.Append(" INSERT INTO UT_Line(LineID,LineCode,LineName,WSID) ");
            strSql.Append(" VALUES(@lnID,@lnCode,@lnName,@WSID) ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@lnID", model.LineID);
                    Db.AddInParameter(cmd, "@lnCode", model.LineCode);
                    Db.AddInParameter(cmd, "@lnName", model.LineName);
                    Db.AddInParameter(cmd, "@WSID", model.WSID);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public bool ModifyLine(LineEntity model)
        {
            bool result = false;
            var strSql = new StringBuilder();
            strSql.Append(" UPDATE UT_Line SET LineCode=@lnCode, LineName=@lnName, WSID=@WSID WHERE LineID =@lnID ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@lnCode", model.LineCode);
                    Db.AddInParameter(cmd, "@lnName", model.LineName);
                    Db.AddInParameter(cmd, "@WSID", model.WSID);
                    Db.AddInParameter(cmd, "@lnID", model.LineID);
                    if (Db.ExecuteNonQuery(cmd) > 0)
                    {
                        result = true;
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }

        public int CheckLineByCode(string lineCode)
        {
            var count = 0;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT COUNT(*)  AS [count] FROM UT_Line  WHERE LineCode=@lnCode ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@lnCode", lineCode);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            count = Convert.ToInt32(reader["count"]);
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return count;
            }
        }

        public LineEntity GetLineInfo(string id)
        {
            var model = new LineEntity();
            object obj = DBNull.Value;
            var strSql = new StringBuilder();
            strSql.Append(" SELECT A.LineID,A.LineCode,A.LineName,A.WSID,B.WSCode,B.WSName FROM UT_Line AS A INNER JOIN UT_WorkShop AS  B ON A.WSID=B.WSID WHERE A.LineID=@lnID ");
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {

                try
                {
                    Db.AddInParameter(cmd, "@lnID", id);
                    using (IDataReader reader = Db.ExecuteReader(cmd))
                    {
                        if (reader.Read())
                        {
                            model.LineID = ReaderHelper.GetValue<Guid>(reader, "LineID");
                            model.LineCode = ReaderHelper.GetValue<string>(reader, "LineCode");
                            model.LineName = ReaderHelper.GetValue<string>(reader, "LineName");
                            model.WSID = ReaderHelper.GetValue<Guid>(reader, "WSID");
                            model.WSCode = ReaderHelper.GetValue<string>(reader, "WSCode");
                            model.WSName = ReaderHelper.GetValue<string>(reader, "WSName"); 
                        }
                    }
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
                return model;
            }
        }


        public bool RemoveLine(string id)
        {
            var strSql = new StringBuilder();
            strSql.Append(" Delete From UT_Line Where LineID=@lnID ");
            var result = false;
            using (var cmd = Db.GetSqlStringCommond(strSql.ToString()))
            {
                try
                {
                    Db.AddInParameter(cmd, "@lnID", id);
                    result = Db.ExecuteNonQuery(cmd) > 0;
                }
                catch (Exception ex)
                {
                    EventsLog.WriteLog("ErrorInfo" + ex.Message);
                }
            }
            return result;
        }
    }
}
